AWS EC2 上の Python から BigQuery にデータをロードしてみた

#AWS

#Amazon EC2

みかみ

2019.11.29

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

GCP ＆ BigQuery 勉強中です。

業務では Python をメインに使用しています。BigQuery にも Python 経由でアクセスしたい！

やりたいこと

EC2 から Python で BigQuery にアクセスしたい
Python コードでファイルデータを BigQuery にロードしたい

やってみた

GCP の準備

クイックスタートに従って、APIを有効にした後、認証情報の設定に進みます。

クイックスタート: クライアントライブラリの使用

認証情報設定画面で「必要な認証情報」ボタンをクリック。

「プロジェクトへの認証情報の追加」画面で、「サービスアカウント名」に任意の文字列を入力し、「役割」で「Project」「オーナー」を選択します。「キーのタイプ」は「JSON」のまま「次へ」ボタンをクリック。

サービスアカウントキー作成完了ポップアップが表示され、キーファイルがPCにダウンロードされました。

作成したサービスアカウントは、GCP コンソール「IAMと管理」の「サービスアカウント」から、確認、編集、削除可能なようです。

EC2の準備

キーファイルパスの設定

EC2（Amazon Linux 2）を立ち上げて、先ほど取得したキーファイルを配置したパスを、環境変数 GOOGLE_APPLICATION_CREDENTIALS に設定します。

再ログインした時に設定しなおす手間を省くため、~/.bash_profile に以下を追記しました。

export GOOGLE_APPLICATION_CREDENTIALS="/home/ec2-user/cm-da-mikami-yuki.json"

プロファイルを読み込みなおして確認してみると・・・

[ec2-user@ip-172-31-31-170 ~]$ source ~/.bash_profile
[ec2-user@ip-172-31-31-170 ~]$ echo $GOOGLE_APPLICATION_CREDENTIALS
/home/ec2-user/cm-da-mikami-yuki.json

ちゃんと環境変数が設定されました。

クライアントライブラリをインストール

Python の BigQuery 操作用ライブラリ google-cloud-bigquery をインストールします。

google-cloud-bigquery 1.22.0 | PyPI

サポートされる Python バージョンは 3.5 以上とのことです。

Amazon Linux 2 にはデフォルトで 3系の Python が入っていないので、まずは Python3 と pip をインストールします。

[ec2-user@ip-172-31-31-170 ~]$ sudo yum install python3
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
amzn2-core                                               | 2.4 kB     00:00
Resolving Dependencies
（省略）
Installed:
  python3.x86_64 0:3.7.4-1.amzn2.0.3

Dependency Installed:
  python3-libs.x86_64 0:3.7.4-1.amzn2.0.3                      python3-pip.noarch 0:9.0.3-1.amzn2.0.1
  python3-setuptools.noarch 0:38.4.0-3.amzn2.0.6

Complete!
[ec2-user@ip-172-31-31-170 ~]$ curl -O https://bootstrap.pypa.io/get-pip.py
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1734k  100 1734k    0     0  18.8M      0 --:--:-- --:--:-- --:--:-- 18.8M
[ec2-user@ip-172-31-31-170 ~]$ python3 get-pip.py --user
Collecting pip
  Downloading https://files.pythonhosted.org/packages/00/b6/9cfa56b4081ad13874b0c6f96af8ce16cfbc1cb06bedf8e9164ce5551ec1/pip-19.3.1-py2.py3-none-any.whl (1.4MB)
     |████████████████████████████████| 1.4MB 3.3MB/s
Collecting wheel
  Downloading https://files.pythonhosted.org/packages/00/83/b4a77d044e78ad1a45610eb88f745be2fd2c6d658f9798a15e384b7d57c9/wheel-0.33.6-py2.py3-none-any.whl
Installing collected packages: pip, wheel
Successfully installed pip-19.3.1 wheel-0.33.6

インストール完了。バージョンを確認してみます。

[ec2-user@ip-172-31-31-170 ~]$ pip -V
pip 19.3.1 from /home/ec2-user/.local/lib/python3.7/site-packages/pip (python 3.7)
[ec2-user@ip-172-31-31-170 ~]$ python --version
Python 2.7.16
[ec2-user@ip-172-31-31-170 ~]$ python3 --version
Python 3.7.4

普通の python コマンドだと 2系が動いてしまうので、デフォルトを3系にするよう、.bash_profile に alias を追加します。

alias python=python3

確認してみると・・・

[ec2-user@ip-172-31-31-170 ~]$ source .bash_profile
[ec2-user@ip-172-31-31-170 ~]$ python --version
Python 3.7.4

デフォルトで 3系が動くようになりました。

続いて virtualenv で google-cloud-bigquery をインストールします。

[ec2-user@ip-172-31-31-170 ~]$ pip install virtualenv --user
Collecting virtualenv
  Using cached https://files.pythonhosted.org/packages/62/77/6a86ef945ad39aae34aed4cc1ae4a2f941b9870917a974ed7c5b6f137188/virtualenv-16.7.8-py2.py3-none-any.whl
Installing collected packages: virtualenv
Successfully installed virtualenv-16.7.8
[ec2-user@ip-172-31-31-170 ~]$ virtualenv test_bq
Using base prefix '/usr'
  No LICENSE.txt / LICENSE found in source
New python executable in /home/ec2-user/test_bq/bin/python3
Also creating executable in /home/ec2-user/test_bq/bin/python
Installing setuptools, pip, wheel...
done.
[ec2-user@ip-172-31-31-170 ~]$ source test_bq/bin/activate
(test_bq) [ec2-user@ip-172-31-31-170 ~]$ test_bq/bin/pip install google-cloud-bigquery
Collecting google-cloud-bigquery
（省略）
Successfully built googleapis-common-protos
Installing collected packages: pyasn1, rsa, pyasn1-modules, cachetools, six, google-auth, pytz, protobuf, googleapis-common-protos, urllib3, idna, certifi, chardet, requests, google-api-core, google-cloud-core, google-resumable-media, google-cloud-bigquery
Successfully installed cachetools-3.1.1 certifi-2019.11.28 chardet-3.0.4 google-api-core-1.14.3 google-auth-1.7.1 google-cloud-bigquery-1.22.0 google-cloud-core-1.0.3 google-resumable-media-0.5.0 googleapis-common-protos-1.6.0 idna-2.8 protobuf-3.11.0 pyasn1-0.4.8 pyasn1-modules-0.2.7 pytz-2019.3 requests-2.22.0 rsa-4.0 six-1.13.0 urllib3-1.25.7

無事、インストールできました。

Python コードから BigQuery にアクセスしてみる

テーブルデータ参照

BigQuery のテーブルデータを参照するサンプルコードをいただいてきて、クエリ部分のみ既存の自分の環境に合わせて修正しました。

from google.cloud import bigquery

client = bigquery.Client()

# Perform a query.
QUERY = (
    'SELECT name FROM `cm-da-mikami-yuki-258308.test_s3.pref` '
    'WHERE code = 15 ')
query_job = client.query(QUERY)  # API request
rows = query_job.result()  # Waits for query to finish

for row in rows:
    print(row.name)

実行してみると・・・

(test_bq) [ec2-user@ip-172-31-31-170 ~]$ python test_select.py
新潟県

データが取得できました！

ファイルデータをロード

こちらからいただいてきた、アメリカの赤ちゃんの名前ファイルの拡張子を .csv に変更＆ヘッダ行を追加して EC2 に配置しました。

Python で、データセットとテーブルを作成してからファイルデータをロードします。

from google.cloud import bigquery

# データセット作成
client = bigquery.Client()
dataset_id = "{}.test_dataset".format(client.project)
dataset = bigquery.Dataset(dataset_id)
dataset.location = "asia-northeast1"

dataset = client.create_dataset(dataset)
print("Created dataset {}.{}".format(client.project, dataset.dataset_id))

# テーブル作成
table_id = "{}.{}.names_1880".format(client.project, dataset.dataset_id)

schema = [
    bigquery.SchemaField("name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("gender", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("count", "INTEGER", mode="REQUIRED"),
]

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

# データロード
filename = '/home/ec2-user/data/yob1880.csv'
dataset_id = dataset_id.split(".")[-1]
table_id = table_id.split(".")[-1]

dataset_ref = client.dataset(dataset_id)
table_ref = dataset_ref.table(table_id)
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1
job_config.autodetect = False

with open(filename, "rb") as source_file:
    job = client.load_table_from_file(source_file, table_ref, job_config=job_config)

job.result()

print("Loaded {} rows into {}:{}.".format(job.output_rows, dataset_id, table_id))

実行してみると・・・

(test_bq) [ec2-user@ip-172-31-31-170 ~]$ python test_load.py
Created dataset cm-da-mikami-yuki-258308.test_dataset
Created table cm-da-mikami-yuki-258308.test_dataset.names_1880
Loaded 2000 rows into test_dataset:names_1880.

ロード完了したようです。

GCP コンソールからも、ロードされたデータを確認できました。